﻿<cfscript>
/** 
* @hint "Entity: ProgramTask, CommonTask"
*/ 

component 
	extends="MachII.framework.Listener" 
	displayname="acadmicManager.listeners.senate.taskMarkListener" 
	output=false 
	depends="taskPropertyAdvice, datetimeAdvice, sessionAdvice, sequenceTaskMarkUnlock,sequenceStudentMark,markAdvice" 
	{
		
	/**
	* @hint "Configures this listener as part of the Mach-II framework"
	*/
	public void function configure() output=false {} 
	

	/**
	* @hint "批准期末成绩任务解锁"
	* 
	* @event "MachII Event Object"
	*/	
	public void function unlockTaskMarkApprove( required MachII.framework.Event event ) output=false {
		
		var signId = event.getArg("REQ");
		
		var tchId = getsessionAdvice().getAutherUserID();
		
		var currentTS = getdatetimeAdvice().getCurrentDatetimeString();
		
		var sql = queryObj = "";
		
		/* Every SQL operation is TRANSACATION*/
		
		transaction {
			
			sql = "	SELECT
						tm.task_id,tm.task_type
					FROM
						t_task_mark_unlock tm
					WHERE
						tm.sign_id = :signId ";
			
			queryObj = new Query(datasource = application.dnsMaster);
			queryObj.addParam(name = "signId", value = signId, cfsqltype = "cf_sql_varchar");
			rs_signEntity = queryObj.execute(sql = sql).getResult();
			
			/* 准备解锁成绩 */
			var taskId = rs_signEntity.task_id;
			var taskType = rs_signEntity.task_type;
			
			sql = "	SELECT
						tm.task_id
					FROM
						t_task_mark tm
					WHERE
						tm.task_id = :taskId ";
			
			queryObj = new Query(datasource = application.dnsMaster);
			queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
			rs_markTaskEntity = queryObj.execute(sql = sql).getResult();


			/* 读取教学任务 */
			/* 读取教学任务默认主讲教师 */
			/* 读取课程计分模式 */
			switch(taskType){
				case "P":
				sql = "	SELECT
							a.tch_id,c.mark_mode
						FROM
							t_task a INNER JOIN t_course c ON a.cid = c.cid
						WHERE
							a.tsk_id = :taskId ";
							
				queryObj = new Query(datasource = application.dnsMaster);
				queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
				rs_taskEntity = queryObj.execute(sql = sql).getResult();
				break;	
				
				case "C":
				sql = "	SELECT
							a.tch_id,c.mark_mode
						FROM
							t_common_task a INNER JOIN t_course c ON a.cid = c.cid
						WHERE
							a.tsk_id = :taskId ";
							
				queryObj = new Query(datasource = application.dnsMaster);
				queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
				rs_taskEntity = queryObj.execute(sql = sql).getResult();
				break;	
				
				case "R":
				sql = "	SELECT
							a.tch_id,c.mark_mode
						FROM
							t_reopen_task a INNER JOIN t_course c ON a.cid = c.cid
						WHERE
							a.tsk_id = :taskId ";
							
				queryObj = new Query(datasource = application.dnsMaster);
				queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
				rs_taskEntity = queryObj.execute(sql = sql).getResult();
				break;	
				
				case "T":
				sql = "	SELECT
							a.tch_id,c.mark_mode
						FROM
							t_sport_task a INNER JOIN t_course c ON a.cid = c.cid
						WHERE
							a.tsk_id = :taskId ";
							
				queryObj = new Query(datasource = application.dnsMaster);
				queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
				rs_taskEntity = queryObj.execute(sql = sql).getResult();
				break;	
			}
			
			
			
			if( rs_markTaskEntity.recordCount ){

				/* 第一步 追加没有临时成绩的学生名单 */
				sql = "	SELECT
							a.scc_id,a.task_type,a.st,a.retaking,
							a.stu_id,
							a.cid,
							a.term_id,
							a.prop_id
						FROM
							t_student_course a
						WHERE
							a.task_id = :taskId 
							AND
							SUBSTR(a.choose_prop,2,1) = '1'
							AND
							NOT EXISTS(
								SELECT 1 FROM t_temp_mark b WHERE
								b.stu_id = a.stu_id
								AND
								a.task_id = b.task_id
							)
						ORDER BY
							a.stu_id";
				
				queryObj = new Query(datasource = application.dnsMaster);
				queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
				rs_chooserAdd = queryObj.execute(sql = sql).getResult();
				
				for( a = 1; a le rs_chooserAdd.recordCount; a++ ){
					
					sql = "	SELECT
								sm.mark_result, sm.mark_note, mark_usual, mark_middle, mark_final 
							FROM
								t_student_mark sm
							WHERE
								sm.scc_id = :chooserId ";
					
					queryObj = new Query(datasource = application.dnsMaster);
					queryObj.addParam(name = "chooserId", value = rs_chooserAdd["scc_id"][a], cfsqltype = "cf_sql_varchar");
					rs_markSaved = queryObj.execute(sql = sql).getResult();

					mark = -1;
					markNote = "1";

					/* 第二步 返回学生已录入成绩 */
					if ( rs_markSaved.recordCount ) {
						mark = rs_markSaved.mark_result;
						markNote = rs_markSaved.mark_note;
						mark_u = rs_markSaved.mark_usual;
						mark_m = rs_markSaved.mark_middle;
						mark_f = rs_markSaved.mark_final;
					}
					
					/* 插入成绩清单 */
					sql = "	INSERT INTO t_temp_mark (
								scc_id,
								stu_id,
								cid,
								term_id,
								prop_id,
								task_type,
								task_id,
								st,
								retaking,
								mark_mode,
								mark_f,
								mark_note_f,
								tch_id,
								mark_u,
								mark_m, 
								mark_note_u, 
								mark_note_m, 
								mark_note 
							)VALUES(
								:chooserId,
								:studentId,
								:courseId,
								:termId,
								:coursePropertyId,
								:taskType,
								:taskId,
								:studyType,
								:retaking,
								:markMode,
								:markFinal,
								'1',
								:teacherId, 
								:markUsual, 
								:markMiddle, 
								'1','1', 
								:markNote 
							)";

					queryObj = new Query(datasource = application.dnsMaster);
					queryObj.addParam(name = "chooserId", value = rs_chooserAdd["scc_id"][a], cfsqltype = "cf_sql_varchar");
					queryObj.addParam(name = "studentId", value = rs_chooserAdd["stu_id"][a], cfsqltype = "cf_sql_varchar");
					queryObj.addParam(name = "courseId", value = rs_chooserAdd["cid"][a], cfsqltype = "cf_sql_varchar");
					queryObj.addParam(name = "termId", value = rs_chooserAdd["term_id"][a], cfsqltype = "cf_sql_char");
					queryObj.addParam(name = "coursePropertyId", value = rs_chooserAdd["prop_id"][a], cfsqltype = "cf_sql_char");
					queryObj.addParam(name = "taskType", value = rs_chooserAdd["task_type"][a], cfsqltype = "cf_sql_char");
					queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
					queryObj.addParam(name = "studyType", value = rs_chooserAdd["st"][a], cfsqltype = "cf_sql_char");
					queryObj.addParam(name = "retaking", value = rs_chooserAdd["retaking"][a], cfsqltype = "cf_sql_char");
					queryObj.addParam(name = "markMode", value = rs_taskEntity.mark_mode, cfsqltype = "cf_sql_char");
					queryObj.addParam(name = "markFinal", value = mark_f, cfsqltype = "cf_sql_decimal", scale = 2);
					queryObj.addParam(name = "markUsual", value = mark_u, cfsqltype = "cf_sql_decimal", scale = 2);
					queryObj.addParam(name = "markMiddle", value = mark_m, cfsqltype = "cf_sql_decimal", scale = 2);
					queryObj.addParam(name = "markNote", value = markNote, cfsqltype = "cf_sql_char" );
					queryObj.addParam(name = "teacherId", value = rs_taskEntity.tch_id, cfsqltype = "cf_sql_varchar" );
					queryObj.execute(sql = sql);
				}

				/* 统计录入进度 */
				sql = "SELECT 
						count(tmm.scc_id)  mark_need,
						SUM(
							CASE
								WHEN tmm.mark_note = '1' and tmm.mark_note_u = '1' and tmm.mark_note_m = '1' and tmm.mark_note_f = '1' and tmm.prop_id = '1' and (tmm.mark_u > 0 OR tmm.mark_u = 0) and (tmm.mark_m > 0 OR tmm.mark_m = 0) and (tmm.mark_f > 0 OR tmm.mark_f = 0) then
									1
								WHEN tmm.mark_note = '1' and tmm.mark_note_u = '1' and tmm.mark_note_m = '1' and tmm.mark_note_f = '1' and tmm.prop_id = '2' and (tmm.mark_u > 0 OR tmm.mark_u = 0) and (tmm.mark_f > 0 OR tmm.mark_f = 0) then
									1
								WHEN tmm.mark_mode = '4' AND (tmm.mark_f > 0 or tmm.mark_f = '0') then
									1 
								WHEN tmm.mark_mode = '2' AND (tmm.mark_f > 0 or tmm.mark_f = '0') then
									1 
								WHEN tmm.mark_note > '1' OR tmm.mark_note < '1' then
									1
								ELSE
									0
								END
							)  mark_done
						FROM t_temp_mark tmm 
						WHERE tmm.task_id = :taskId";
				
				queryObj = new Query(datasource = application.dnsMaster);
				queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
				rs_check = queryObj.execute(sql = sql).getResult();
				
				/* 第五步 标准任务为解锁状态 */
				/* 记录教学任务解锁时间 */
				sql = "	UPDATE 
							t_task_mark t
						SET
							t.mark_done = :markDone ,
							t.mark_need = :markNeed ,
							t.task_prog = :taskProg ,
							t.time_unlock = :timeUnLock 
						WHERE
							t.task_id = :taskId ";
					
				queryObj = new Query(datasource = application.dnsMaster);
				queryObj.addParam(name = "markDone", value = rs_check.mark_done, cfsqltype = "cf_sql_integer");
				queryObj.addParam(name = "markNeed", value = rs_check.mark_need, cfsqltype = "cf_sql_integer");
				queryObj.addParam(name = "taskProg", value = "4", cfsqltype = "cf_sql_char");
				queryObj.addParam(name = "timeUnLock", value = getdatetimeAdvice().getCurrentDatetimeString(), cfsqltype = "cf_sql_char");
				queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
				queryObj.execute(sql = sql);
					
			}

			/* 完成审批标注 */
			sql = "	UPDATE
						t_task_mark_unlock t
					SET
						t.admin_user_id = :adminUserId,
						t.sign_status = 'P',
						t.date_review = :dateReview 
					WHERE
						t.sign_id = :signId ";
			
			queryObj = new Query(datasource = application.dnsMaster);
			queryObj.addParam(name = "adminUserId", value = tchId, cfsqltype = "cf_sql_varchar");
			queryObj.addParam(name = "dateReview", value = currentTS, cfsqltype = "cf_sql_char");
			queryObj.addParam(name = "signId", value = signId, cfsqltype = "cf_sql_char");
			queryObj.execute(sql = sql);
			
		}
		
		
	}
	
	
	/**
	* @hint "拒绝期末成绩任务解锁"
	* 
	* @event "MachII Event Object"
	*/	
	public void function unlockTaskMarkReject( required MachII.framework.Event event ) output=false {
		
		var signId = event.getArg("REQ");
		
		var tchId = getsessionAdvice().getAutherUserID();
		
		var currentTS = getdatetimeAdvice().getCurrentDatetimeString();
		
		var sql = queryObj = "";
		
		/* Every SQL operation is TRANSACATION*/
		transaction {
			
			sql = "	UPDATE
						t_task_mark_unlock t
					SET
						t.admin_user_id = :adminId,
						t.sign_status = :signStatus,
						t.date_review = :dateReview 
					WHERE
						t.sign_id = :signId ";
			
			queryObj = new Query(datasource = application.dnsMaster);
			queryObj.addParam(name = "signId", value = signId, cfsqltype = "cf_sql_char");
			queryObj.addParam(name = "adminId", value = tchId, cfsqltype = "cf_sql_varchar");
			queryObj.addParam(name = "signStatus", value = "B", cfsqltype = "cf_sql_char");
			queryObj.addParam(name = "dateReview", value = currentTS, cfsqltype = "cf_sql_char");
			queryObj.execute(sql = sql);
			
		}
		
	}


	/**
	* @hint "申请解锁期末成绩提交任务"
	* 
	* @event "MachII Event Object"
	*/	
	public void function unlockTaskMarkSign( required MachII.framework.Event event ) output=false {
		
		var taskId = event.getArg("TaskID");
		var taskType = event.getArg("TaskType");
			
		var tchId = getsessionAdvice().getAutherUserID();
		var currentTS = getdatetimeAdvice().getCurrentDatetimeString();
		
		var squenceUnlock = getsequenceTaskMarkUnlock();
		
		/* Every SQL operation is TRANSACATION*/
		transaction {

			sql = "	SELECT 
						tch.tch_id,
						tch.tch_name
					FROM
						t_teacher tch
					WHERE
						tch.tch_id = :teacherId ";

			queryObj = new Query(datasource = application.dnsMaster);
			queryObj.addParam(name="teacherId", value=tchId, cfsqltype="cf_sql_varchar");
			rs_teacher = queryObj.execute(sql=sql).getResult();

			sql = "	SELECT
						ttm.task_id,
						ttm.task_type,
						ttm.term_id,
						ttm.cid
					FROM
						t_task_mark ttm
					WHERE
						ttm.task_id = :taskId ";

			queryObj = new Query(datasource = application.dnsMaster);
			
			queryObj.addParam(name="taskId", value=taskId, cfsqltype="cf_sql_varchar");
			
			rs_task = queryObj.execute(sql=sql).getResult();
			
			sql = "	INSERT INTO t_task_mark_unlock
					  (sign_id,
					   term_id,
					   cid,
					   task_id,
					   task_type,
					   sign_reason,
					   sign_user_id,
					   admin_user_id,
					   sign_status,
					   date_created,
					   date_review)
					values
					  (
					  	:signId ,
						:termId ,
						:courseId ,
						:taskId ,
						:taskType ,
						:signReason ,
						:signUserId ,
						:adminUserId ,
						:signStatus ,
						:dateCreated ,
						:dateReview 
					  )";
			
			signId = squenceUnlock.getID();

			insertObj = new Query(datasource = application.dnsMaster);
			
			insertObj.addParam(name="signId", value=signId, cfsqltype="cf_sql_char");
			insertObj.addParam(name="termId", value=rs_task.term_id, cfsqltype="cf_sql_char");
			insertObj.addParam(name="courseId", value=rs_task.cid,cfsqltype="cf_sql_varchar");
			insertObj.addParam(name="taskId", value=taskId, cfsqltype="cf_sql_varchar");
			insertObj.addParam(name="taskType", value=taskType, cfsqltype="cf_sql_char");
			insertObj.addParam(name="signReason", value=event.getArg("ChangeReason", "未填写"), cfsqltype="cf_sql_varchar");
			insertObj.addParam(name="signUserId", value=rs_teacher.tch_id, cfsqltype="cf_sql_varchar");
			insertObj.addParam(name="adminUserId", value=rs_teacher.tch_id, cfsqltype="cf_sql_varchar");
			insertObj.addParam(name="signStatus", value='W', cfsqltype="cf_sql_char");
			insertObj.addParam(name="dateCreated", value=currentTS, cfsqltype="cf_sql_char");
			insertObj.addParam(name="dateReview", value='00000000000000', cfsqltype="cf_sql_char");
			
			insertObj.execute(sql=sql);

			var passArgs = structNew();
			structInsert( passArgs, "REQ", signId, true );

			event.setArgs( passArgs );
				
		}
		
	}


	/**
	* @hint "解锁期末成绩提交任务"
	* 
	* @event "MachII Event Object"
	*/	
	public void function unlockTaskMark( required MachII.framework.Event event ) output=false {
			
		var taskId = event.getArg("TSK");
		var taskType = event.getArg("TT");
		
		var queryObj = sql = "";
				
		/* Every SQL operation is TRANSACATION*/
		transaction {
				
			sql = "	SELECT
						tm.task_id
					FROM
						t_task_mark tm
					WHERE
						tm.task_id = :taskId ";
			
			queryObj = new Query(datasource = application.dnsMaster);
			queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
			rs_markTaskEntity = queryObj.execute(sql = sql).getResult();

			/* 读取教学任务 */
			switch(taskType){
				case "P":
				sql = "	SELECT
							a.tch_id,c.mark_mode
						FROM
							t_task a INNER JOIN t_course c ON a.cid = c.cid
						WHERE
							a.tsk_id = :taskId ";
							
				queryObj = new Query(datasource = application.dnsMaster);
				queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
				rs_taskEntity = queryObj.execute(sql = sql).getResult();
				break;	
				
				case "C":
				sql = "	SELECT
							a.tch_id,c.mark_mode
						FROM
							t_common_task a INNER JOIN t_course c ON a.cid = c.cid
						WHERE
							a.tsk_id = :taskId ";
							
				queryObj = new Query(datasource = application.dnsMaster);
				queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
				rs_taskEntity = queryObj.execute(sql = sql).getResult();
				break;	
				
				case "R":
				sql = "	SELECT
							a.tch_id,c.mark_mode
						FROM
							t_reopen_task a INNER JOIN t_course c ON a.cid = c.cid
						WHERE
							a.tsk_id = :taskId ";
							
				queryObj = new Query(datasource = application.dnsMaster);
				queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
				rs_taskEntity = queryObj.execute(sql = sql).getResult();
				break;	
				
				case "T":
				sql = "	SELECT
							a.tch_id,c.mark_mode
						FROM
							t_sport_task a INNER JOIN t_course c ON a.cid = c.cid
						WHERE
							a.tsk_id = :taskId ";
							
				queryObj = new Query(datasource = application.dnsMaster);
				queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
				rs_taskEntity = queryObj.execute(sql = sql).getResult();
				break;	
			}
			
			
			/* 读取教学任务默认主讲教师 */
			/* 读取课程计分模式 */
				
			if ( rs_markTaskEntity.recordCount ) {

				/* 第一步 追加学生名单 */
				sql = "	SELECT
							a.scc_id,a.task_type,a.st,a.retaking,
							a.stu_id,
							a.cid,
							a.term_id,
							a.prop_id 
						FROM
							t_student_course a
						WHERE
							a.task_id = :taskId 
							AND
							SUBSTR(a.choose_prop,2,1) = '1'
							AND
							NOT EXISTS(
								SELECT 1 FROM t_temp_mark b WHERE
								b.stu_id = a.stu_id
								AND
								a.task_id = b.task_id
							)
						ORDER BY
							a.stu_id";
				
				queryObj = new Query(datasource = application.dnsMaster);
				queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
				rs_chooserAdd = queryObj.execute(sql = sql).getResult();
				
				for( a = 1; a le rs_chooserAdd.recordCount; a++ ){
					
					sql = "	SELECT
								sm.mark_result, sm.mark_note, mark_usual, mark_middle, mark_final 
							FROM
								t_student_mark sm
							WHERE
								sm.scc_id = :chooserId ";
					
					queryObj = new Query(datasource = application.dnsMaster);
					queryObj.addParam(name = "chooserId", value = rs_chooserAdd["scc_id"][a], cfsqltype = "cf_sql_varchar");
					rs_markSaved = queryObj.execute(sql = sql).getResult();

					mark = -1;
					markNote = "1";

					/* 第二步 返回学生已录入成绩 */
						
					if ( rs_markSaved.recordCount ) {
						mark = rs_markSaved.mark_result;
						markNote = rs_markSaved.mark_note;
						mark_u = rs_markSaved.mark_usual;
						mark_m = rs_markSaved.mark_middle;
						mark_f = rs_markSaved.mark_final;
					}

					sql = "	INSERT INTO t_temp_mark (
								scc_id,
								stu_id,
								cid,
								term_id,
								prop_id,
								task_type,
								task_id,
								st,
								retaking,
								mark_mode,
								mark_f,
								mark_note_f,
								tch_id,
								mark_u,
								mark_m, 
								mark_note_u, 
								mark_note_m, 
								mark_note 
							)VALUES(
								:chooserId,
								:studentId,
								:courseId,
								:termId,
								:coursePropertyId,
								:taskType,
								:taskId,
								:studyType,
								:retaking,
								:markMode,
								:markFinal,
								'1',
								:teacherId, 
								:markUsual, 
								:markMiddle, 
								'1','1', 
								:markNote 
							)";

					queryObj = new Query(datasource = application.dnsMaster);
					queryObj.addParam(name = "chooserId", value = rs_chooserAdd["scc_id"][a], cfsqltype = "cf_sql_varchar");
					queryObj.addParam(name = "studentId", value = rs_chooserAdd["stu_id"][a], cfsqltype = "cf_sql_varchar");
					queryObj.addParam(name = "courseId", value = rs_chooserAdd["cid"][a], cfsqltype = "cf_sql_varchar");
					queryObj.addParam(name = "termId", value = rs_chooserAdd["term_id"][a], cfsqltype = "cf_sql_char");
					queryObj.addParam(name = "coursePropertyId", value = rs_chooserAdd["prop_id"][a], cfsqltype = "cf_sql_char");
					queryObj.addParam(name = "taskType", value = rs_chooserAdd["task_type"][a], cfsqltype = "cf_sql_char");
					queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
					queryObj.addParam(name = "studyType", value = rs_chooserAdd["st"][a], cfsqltype = "cf_sql_char");
					queryObj.addParam(name = "retaking", value = rs_chooserAdd["retaking"][a], cfsqltype = "cf_sql_char");
					queryObj.addParam(name = "markMode", value = rs_taskEntity.mark_mode, cfsqltype = "cf_sql_char");
					queryObj.addParam(name = "markFinal", value = mark_f, cfsqltype = "cf_sql_decimal", scale = 2);
					queryObj.addParam(name = "markUsual", value = mark_u, cfsqltype = "cf_sql_decimal", scale = 2);
					queryObj.addParam(name = "markMiddle", value = mark_m, cfsqltype = "cf_sql_decimal", scale = 2);
					queryObj.addParam(name = "markNote", value = markNote, cfsqltype = "cf_sql_char" );
					queryObj.addParam(name = "teacherId", value = rs_taskEntity.tch_id, cfsqltype = "cf_sql_varchar" );
					queryObj.execute(sql = sql);
					
				}

				/* 第三步 删除多余名单*/

				/* 第四步 更新成绩提交数量 */
				sql = "SELECT 
						count(tmm.scc_id)  mark_need,
						SUM(
							CASE
								WHEN tmm.mark_note = '1' and tmm.mark_note_u = '1' and tmm.mark_note_m = '1' and tmm.mark_note_f = '1' and tmm.prop_id = '1' and (tmm.mark_u > 0 OR tmm.mark_u = 0) and (tmm.mark_m > 0 OR tmm.mark_m = 0) and (tmm.mark_f > 0 OR tmm.mark_f = 0) then
									1
								WHEN tmm.mark_note = '1' and tmm.mark_note_u = '1' and tmm.mark_note_m = '1' and tmm.mark_note_f = '1' and tmm.prop_id = '2' and (tmm.mark_u > 0 OR tmm.mark_u = 0) and (tmm.mark_f > 0 OR tmm.mark_f = 0) then
									1
								WHEN tmm.mark_mode = '4' AND (tmm.mark_f > 0 or tmm.mark_f = '0') then
									1 
								WHEN tmm.mark_mode = '2' AND (tmm.mark_f > 0 or tmm.mark_f = '0') then
									1 
								WHEN tmm.mark_note > '1' OR tmm.mark_note < '1' then
									1
								ELSE
									0
								END
							)  mark_done
						FROM t_temp_mark tmm 
						WHERE tmm.task_id = :taskId";
				
				queryObj = new Query(datasource = application.dnsMaster);
				queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
				rs_check = queryObj.execute(sql = sql).getResult();
				
				/* 第五步 标准任务为解锁状态 */
				/* 记录教学任务解锁时间 */
				sql = "	UPDATE 
							t_task_mark t
						SET
							t.mark_done = :markDone ,
							t.mark_need = :markNeed ,
							t.task_prog = :taskProg ,
							t.time_unlock = :timeUnLock 
						WHERE
							t.task_id = :taskId ";
					
				queryObj = new Query(datasource = application.dnsMaster);
				queryObj.addParam(name = "markDone", value = rs_check.mark_done, cfsqltype = "cf_sql_integer");
				queryObj.addParam(name = "markNeed", value = rs_check.mark_need, cfsqltype = "cf_sql_integer");
				queryObj.addParam(name = "taskProg", value = "4", cfsqltype = "cf_sql_char");
				queryObj.addParam(name = "timeUnLock", value = getdatetimeAdvice().getCurrentDatetimeString(), cfsqltype = "cf_sql_char");
				queryObj.addParam(name = "taskId", value = taskId, cfsqltype = "cf_sql_varchar");
				queryObj.execute(sql = sql);
				
			}
					
		}
			
	}
	
	/**
	* @hint "加锁期末成绩提交任务"
	* 
	* @event "MachII Event Object"
	*/	
	public void function lockTaskMark( required MachII.framework.Event event ) output=false {
			
		var taskId = event.getArg("TSK");
		var taskType = event.getArg("TT");
		
		var queryObj = sql = "";
		
		var sequenceStudentMark = getsequenceStudentMark();
		var datetimeAdvice = getdatetimeAdvice();
		var markAdvice = getmarkAdvice();
				
		/* Every SQL operation is TRANSACATION*/
		transaction {
				
			/* 载入期末成绩提交任务 */
			 sql = "	SELECT
						 tm.task_id,
						 tm.task_prog
					 FROM
						 t_task_mark tm
					 WHERE
						 tm.task_id = :taskId ";
						 
			 queryObj = new Query(datasource = application.dnsSlave);
			 
			 queryObj.addParam(name="taskId", value=taskId, cfsqltype="cf_sql_varchar");
			 
			 rs_task = queryObj.execute(sql=sql).getResult();

			if ( rs_task.task_prog eq "4" ) {
				   
				    /* 更新成绩提交状态为 3 已提交 */
				    /* 设置成绩提交时间 */

				    sql = "	UPDATE t_task_mark t
						    SET
							    t.task_prog = :taskProgress ,
							    t.time_submit = :timeSubmit 
						    WHERE
							    t.task_id = :taskId ";
							    
				    updateObj = new Query(datasource = application.dnsMaster);
				    
				    updateObj.addParam(name="taskId", value=taskId, cfsqltype="cf_sql_varchar");
				    updateObj.addParam(name="taskProgress", value="3", cfsqltype="cf_sql_char");
				    updateObj.addParam(name="timeSubmit", value=datetimeAdvice.getCurrentDatetimeString(), cfsqltype="cf_sql_char");
				    
				    updateObj.execute(sql = sql);
				    

				    /* 转置成绩到正式成绩记录 */

				    sql = "	SELECT tmm.retaking,
							    tmm.mark_mode,
							    tmm.mark_u,
							    tmm.mark_m,
							    tmm.mark_f,
							    tmm.mark_note,
							    tmm.task_type,
							    tmm.task_id,
							    tmm.scc_id,
							    tmm.stu_id,
							    tmm.prop_id,
							    c.cid,
							    tmm.term_id,
							    tmm.tch_id,
							    c.course_credit
						    FROM t_temp_mark tmm
							    INNER JOIN t_course c ON c.cid = tmm.cid
						    WHERE
							    tmm.task_id = :taskId 
						    ORDER BY
							    tmm.stu_id ";

				    queryObj = new Query(datasource = application.dnsMaster);
				    
				    queryObj.addParam(name="taskId", value=taskId, cfsqltype="cf_sql_varchar");
				    rs_mark = queryObj.execute(sql=sql).getResult();
				    
				    timeStamp = datetimeAdvice.getCurrentDatetimeString();

				    for( a = 1; a LTE rs_mark.recordCount; a++ ){
					    
					    sequenceStudentMark.setStudentID(rs_mark['stu_id'][a]);
					    sequenceStudentMark.setCourseID(rs_mark['cid'][a]);
					    sequenceStudentMark.setTermID(rs_mark['term_id'][a]);
					    sequenceStudentMark.setTaskType(rs_mark['task_type'][a]);
					    
					    markID = sequenceStudentMark.getID();
					    
					    sql = "	SELECT
								    t.scm_id
							    FROM
								    t_student_mark t
							    WHERE
								    t.scm_id = :markId ";

					    queryObj = new Query(datasource = application.dnsMaster);
					    
					    queryObj.addParam(name="markId", value=markID, cfsqltype="cf_sql_varchar");
					    
					    rs_check = queryObj.execute(sql=sql).getResult();
					    
					    if( rs_check.recordCount eq 0 ){

						    sql = "INSERT INTO t_student_mark
											 (scm_id,
											  stu_id,
											  term_id,
											  cid,
											  prop_id,
											  retaking,
											  mark_mode,
											  mark_result,
											  mark_note,
											  mark_resit,
											  mark_renote,
											  task_type,
											  task_id,
											  tch_id,
											  mark_time,
											  mark_resit_time,
											  scc_id,
											  mark_resit_limit,
											  remarks,
											  credit_point,
											  credit, 
											  credit_point_o,
											  mark_usual,
											  mark_middle, 
											  mark_final 
											  )
										    values
											 (:markID ,
											  :studentId ,
											  :termId ,
											  :courseId ,
											  :propId ,
											  :retaking ,
											  :markMode ,
											  :markResult ,
											  :markNote ,
											  :markResit ,
											  :markRenote ,
											  :taskType ,
											  :taskId ,
											  :teacherId ,
											  :markTime ,
											  :markResitTime ,
											  :chooserId ,
											  :markResitLimit,
											  :remarks, 
											  :creditPoint,
											  :credit, 
											  :creditPointO,
											  :markUsual,
											  :markMiddle, 
											  :markFinal )";
						    
					    }
					    else {
						    sql ="UPDATE t_student_mark t
									  SET 
										  t.stu_id= :studentId,
										  t.term_id = :termId,
										  t.cid= :courseId,
										  t.prop_id= :propId,
										  t.retaking= :retaking,
										  t.mark_mode= :markMode,
										  t.mark_result= :markResult,
										  t.mark_note= :markNote,
										  t.mark_resit= :markResit,
										  t.mark_renote= :markRenote,
										  t.task_type= :taskType,
										  t.task_id= :taskId,
										  t.tch_id= :teacherId,
										  t.mark_time= :markTime,
										  t.mark_resit_time= :markResitTime,
										  t.scc_id= :chooserId,
										  t.mark_resit_limit = :markResitLimit,
										  t.remarks = :remarks,
										  t.credit_point = :creditPoint,
										  t.credit = :credit, 
										  t.credit_point_o = :creditPointO, 
										  t.mark_usual = :markUsual, 
										  t.mark_middle = :markMiddle, 
										  t.mark_final = :markFinal 
									WHERE 
										  t.scm_id = :markID ";

					    }
					    
					    /* 根据备注规范 */
					    mark = 0;
				    
					    /* 2 - 缺考 */
					    if ( rs_mark['mark_note'][a] eq "2" ) {
						    mark = -1;
					    }
					    
					    /* 3 - 缓考 */
					    if ( rs_mark['mark_note'][a] eq "3" ) {
						    mark = -1;
					    }
					    
					    /* 9 - 违纪 */
					    if ( rs_mark['mark_note'][a] eq "9" ) {
						    mark = -1;
					    }
					    
					    
					    /* 规范百分制成绩分数 */
					    if ( rs_mark['mark_mode'][a] eq "0" and rs_mark['mark_note'][a] eq "1" ){
						    
						    if( rs_mark['prop_id'][a] eq "1" ){ //必修
						    
							    mark = ( rs_mark['mark_u'][a] * 20 + rs_mark['mark_m'][a] * 30 + rs_mark['mark_f'][a] * 50 )/100;
							    
						    }else if ( rs_mark['prop_id'][a] eq "2" ){//选修
						    
							    mark = ( rs_mark['mark_u'][a] * 20 + rs_mark['mark_f'][a] * 80 )/100;
							    
						    }
					    }
					    
					    /* 规范二级制和五级制成绩分数 */
					    if ( (rs_mark['mark_mode'][a] eq "2" or rs_mark['mark_mode'][a] eq "4")  and rs_mark['mark_note'][a] eq "1" ) {
						    
							    mark = rs_mark['mark_f'][a];
						    
					    }
					    
					    courseCredit = rs_mark['course_credit'][a];
					    
					    if( mark lt 60){
						    courseCredit = 0;	
					    }
				    
					    queryObj = new Query(datasource = application.dnsMaster);

					    queryObj.addParam(name="markID", value=markID, cfsqltype="cf_sql_varchar");
					    queryObj.addParam(name="studentId", value=rs_mark['stu_id'][a], cfsqltype="cf_sql_varchar");
					    queryObj.addParam(name="termId", value=rs_mark['term_id'][a], cfsqltype="cf_sql_char");
					    queryObj.addParam(name="courseId", value=rs_mark['cid'][a], cfsqltype="cf_sql_varchar");
					    queryObj.addParam(name="propId", value=rs_mark['prop_id'][a], cfsqltype="cf_sql_char");
					    queryObj.addParam(name="retaking", value=rs_mark['retaking'][a], cfsqltype="cf_sql_char");
					    queryObj.addParam(name="markMode", value=rs_mark['mark_mode'][a], cfsqltype="cf_sql_char");
					    queryObj.addParam(name="markResult", value=mark, cfsqltype="cf_sql_varchar");
					    queryObj.addParam(name="markNote", value=rs_mark['mark_note'][a], cfsqltype="cf_sql_char");
					    queryObj.addParam(name="markResit", value=-1, cfsqltype="cf_sql_varchar");
					    queryObj.addParam(name="markRenote", value=1, cfsqltype="cf_sql_char");
					    queryObj.addParam(name="taskType", value=rs_mark['task_type'][a], cfsqltype="cf_sql_char");
					    queryObj.addParam(name="taskId", value=rs_mark['task_id'][a], cfsqltype="cf_sql_varchar");
					    queryObj.addParam(name="teacherId", value=rs_mark['tch_id'][a], cfsqltype="cf_sql_varchar");
					    queryObj.addParam(name="markTime", value=timeStamp, cfsqltype="cf_sql_char");
					    queryObj.addParam(name="markResitTime", value=timeStamp, cfsqltype="cf_sql_char");
					    queryObj.addParam(name="chooserId", value=rs_mark['scc_id'][a], cfsqltype="cf_sql_varchar");
					    queryObj.addParam(name="markResitLimit", value=100, cfsqltype="cf_sql_decimal", scale=2);
					    queryObj.addParam(name="remarks", value="", cfsqltype="cf_sql_varchar");
					    queryObj.addParam( name="creditPoint", value=markAdvice.getMarkCredit(rs_mark['mark_mode'][a], rs_mark['course_credit'][a], mark, -1, 100), cfsqltype="cf_sql_decimal", scale=4 );
					    queryObj.addParam(name="credit", value=courseCredit, cfsqltype="cf_sql_integer");
					    queryObj.addParam(name="creditPointO", value=courseCredit * markAdvice.getMarkCredit(rs_mark['mark_mode'][a], rs_mark['course_credit'][a], mark, -1, 100), cfsqltype="cf_sql_varchar");
					    queryObj.addParam(name="markUsual", value=rs_mark['mark_u'][a], cfsqltype="cf_sql_decimal", scale=2);
					    queryObj.addParam(name="markMiddle", value=rs_mark['mark_m'][a], cfsqltype="cf_sql_decimal", scale=2);
					    queryObj.addParam(name="markFinal", value=rs_mark['mark_f'][a], cfsqltype="cf_sql_decimal", scale=2);
					    
					    queryObj.execute(sql=sql);

				    }
				    
				    /* 删除临时成绩记录 */
				    for( b = 1; b LTE rs_mark.recordCount; b++ ){
					    sql = "	DELETE FROM
								    t_temp_mark t
							    WHERE 
								    t.scc_id = :chooserId ";

					    queryObj = new Query(datasource = application.dnsMaster);
					    
					    queryObj.addParam(name="chooserId", value=rs_mark['scc_id'][b], cfsqltype="cf_sql_varchar");
					    
					    queryObj.execute(sql=sql);
				    }
				    
			   }
					
		}
			
	}
	
	/**
	* @hint "刷新教学任务成绩提交数量"
	* 
	* @event "MachII Event Object"
	*/	
	public void function refreshTaskMark( required MachII.framework.Event event ) output=false {
		
		var taskId = event.getArg("TSK");
		
		/* Every SQL operation is TRANSACATION*/
		transaction {
			
			/* 更新成绩提交数量，先查询数量 */
			sql = "	SELECT 
						count(tmm.scc_id)  mark_need,
						SUM(
							CASE
								WHEN tmm.mark_note = '1' and tmm.mark_note_u = '1' and tmm.mark_note_m = '1' and tmm.mark_note_f = '1' and tmm.prop_id = '1' and (tmm.mark_u > 0 OR tmm.mark_u = 0) and (tmm.mark_m > 0 OR tmm.mark_m = 0) and (tmm.mark_f > 0 OR tmm.mark_f = 0) then
									1
								WHEN tmm.mark_note = '1' and tmm.mark_note_u = '1' and tmm.mark_note_m = '1' and tmm.mark_note_f = '1' and tmm.prop_id = '2' and (tmm.mark_u > 0 OR tmm.mark_u = 0) and (tmm.mark_f > 0 OR tmm.mark_f = 0) then
									1
								WHEN tmm.mark_mode = '4' AND (tmm.mark_f > 0 or tmm.mark_f = '0') then
									1 
								WHEN tmm.mark_mode = '2' AND (tmm.mark_f > 0 or tmm.mark_f = '0') then
									1 
								WHEN tmm.mark_note > '1' OR tmm.mark_note < '1' then
									1
								ELSE
									0
								END
							)  mark_done
						FROM t_temp_mark tmm 
						WHERE tmm.task_id = :taskId ";		
			queryObj = new Query(datasource = application.dnsMaster);
			queryObj.addParam(name="taskId", value=taskId, cfsqltype="cf_sql_char");
			rs_task_mark = queryObj.execute(sql = sql).getResult();
						
			sql = "	UPDATE t_task_mark t
						SET t.mark_need = :markNeed , t.mark_done = :markDone 
						WHERE t.task_id = :taskId ";
			updateObj = new Query(datasource = application.dnsMaster);
			updateObj.addParam(name="markNeed", value=rs_task_mark.mark_need, cfsqltype="cf_sql_integer");
			updateObj.addParam(name="markDone", value=rs_task_mark.mark_done, cfsqltype="cf_sql_integer");
			updateObj.addParam(name="taskId", value=taskId, cfsqltype="cf_sql_varchar");
			updateObj.execute(sql=sql);
			
		}
		
	}

	/**
	* @hint "初始化教学任务成绩提交状态"
	* 
	* @event "MachII Event Object"
	*/	
	public void function initTaskMark( required MachII.framework.Event event ) output=false {
			
		var taskId = event.getArg("TSK");
		var taskType = event.getArg("TT");

		var taskProp = gettaskPropertyAdvice();
			
		/* Every SQL operation is TRANSACATION*/
		transaction {
				
			/* 同时更新教学任务 标志为禁止学生选课 禁止学生退课 */
			
			switch(taskType){
					case 'C':
						sql = "SELECT
							tsk.tsk_id,
							tsk.task_prop
						FROM
							t_common_task tsk
						WHERE
							 tsk.tsk_id = :taskId ";
						break;
					case 'P':
						sql = "SELECT
							tsk.tsk_id,
							tsk.task_prop
						FROM
							t_task tsk
						WHERE
							 tsk.tsk_id = :taskId ";
						break;
					case 'R':
						sql = "SELECT
							tsk.tsk_id,
							tsk.task_prop
						FROM
							t_reopen_task tsk
						WHERE
							 tsk.tsk_id = :taskId ";
						break;
					case 'T':
						sql = "SELECT
							tsk.tsk_id,
							tsk.task_prop
						FROM
							t_sport_task tsk
						WHERE
							 tsk.tsk_id = :taskId ";
						break;
				}
			queryObj = new Query(datasource = application.dnsMaster);
			queryObj.addParam(name ="taskId", value=taskId, cfsqltype="cf_sql_varchar");
			rs_task = queryObj.execute(sql = sql).getResult();
			
			/* 设置教学任务禁止学生选课 即不可新增学生 */
			/* 设置禁止学生退课 即学生不可退选课程*/
			if(rs_task.recordCount GT 0){
				taskProp.parseProperty( rs_task['task_prop'][1] );
				taskProp.setSubscribe(true);
				taskProp.setDischarge(true);
				
				switch(taskType){
					case 'C':
						sql = "	UPDATE t_common_task tsk
									SET tsk.task_prop = :taskProp 
								WHERE
							 		tsk.tsk_id = :taskId ";
									
						updateObj = new Query(datasource = application.dnsMaster);
						
						updateObj.addParam(name="taskId", value=taskId, cfsqltype="cf_sql_varchar");
						updateObj.addParam(name="taskProp", value=taskProp.stringProperty(), cfsqltype="cf_sql_char");
						
						updateObj.execute(sql=sql);
						
						break;
					case 'P':
						sql = "	UPDATE t_task tsk
									SET tsk.task_prop = :taskProp 
								WHERE
							 		tsk.tsk_id = :taskId ";
									
						updateObj = new Query(datasource = application.dnsMaster);
						
						updateObj.addParam(name="taskId", value=taskId, cfsqltype="cf_sql_varchar");
						updateObj.addParam(name="taskProp", value=taskProp.stringProperty(), cfsqltype="cf_sql_char");
						
						updateObj.execute(sql=sql);
						
						break;
					case 'R':
						sql = "	UPDATE t_reopen_task tsk
									SET tsk.task_prop = :taskProp 
								WHERE
							 		tsk.tsk_id = :taskId ";

						updateObj = new Query(datasource = application.dnsMaster);
						
						updateObj.addParam(name="taskId", value=taskId, cfsqltype="cf_sql_varchar");
						updateObj.addParam(name="taskProp", value=taskProp.stringProperty(), cfsqltype="cf_sql_char");
						
						updateObj.execute(sql=sql);
						
						break;
					case 'T':
						sql = "	UPDATE t_sport_task tsk
									SET tsk.task_prop = :taskProp 
								WHERE
							 		tsk.tsk_id = :taskId ";
									
						updateObj = new Query(datasource = application.dnsMaster);
						
						updateObj.addParam(name="taskId", value=taskId, cfsqltype="cf_sql_varchar");
						updateObj.addParam(name="taskProp", value=taskProp.stringProperty(), cfsqltype="cf_sql_char");
						
						updateObj.execute(sql=sql);
						
						break;
				}
				
				
			}	
		}		
	}
			
}

</cfscript>